IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetArraPIsForCommunicationByDAAdmPersonId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[GetArraPIsForCommunicationByDAAdmPersonId]
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetArraPIsForCommunicationByDAAdmPersonId]     
(
	@DAAdmPersonId INT
)                           
AS BEGIN                                
	SET NOCOUNT ON 

	SELECT DISTINCT 
	adp.Id,adp.FirstName,adp.LastName,aea.EmailAddress,aagmt.DepartmentManagerAdmPersonId
	FROM arraAgreement aagmt 
	INNER JOIN agrAgreement_fct afct ON aagmt.FundId = afct.FundId
	INNER JOIN admPerson adp ON adp.Id = afct.PrincipalInvestigatorId
	INNER JOIN ArraQuarterlyReportStatus aqrs ON aqrs.Id = aagmt.ArraQuarterlyReportStatusId
	INNER JOIN ArraPIQuestionnaire apiq ON apiq.ArraAgreementId = aagmt.Id
	INNER JOIN ArraPIQuestionnaireEventHistory apiqeh ON apiqeh.ArraPIQuestionnaireId = apiq.Id
	INNER JOIN ArraPIQuestionnaireEvent apiqe ON apiqe.Id = apiqeh.ArraPIQuestionnaireEventId
	LEFT JOIN AdmEmailAddress aea ON aea.AdmPersonId = adp.Id
	WHERE
		aagmt.DepartmentManagerAdmPersonId = @DAAdmPersonId
	AND	aqrs.QuarterEndDate = (SELECT MIN(aqrs1.QuarterEndDate) FROM ArraQuarterlyReportStatus aqrs1 WHERE aqrs.IsFinalReport = aqrs1.IsFinalReport)		
	AND aqrs.IsFinalReport = 0
	AND aea.Id = (SELECT MAX(aea1.Id) FROM AdmEmailAddress aea1 WHERE aea1.AdmPersonId = adp.Id AND aea1.admEmailAddressTypeId = 1)
	AND apiqeh.Id = (SELECT MAX(apiqeh1.Id) FROM ArraPIQuestionnaireEventHistory apiqeh1 WHERE apiqeh1.ArraPIQuestionnaireId = apiq.Id)
	AND apiqeh.ArraPIQuestionnaireEventId != 4
	ORDER BY adp.Id

END

--EXEC [GetArraPIsForCommunicationByDAAdmPersonId] 1040  2853 19607

